package com.vfashion.dao.impl.mysql;


import com.vfashion.dao.ClienteDAO;
import com.vfashion.dao.factory.DAOFactory;
import com.vfashion.domain.Cliente;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author danjoas
 */

public class MysqlClienteDAO implements ClienteDAO {

    private Connection mysql;

    public MysqlClienteDAO() {
        mysql = DAOFactory.getInst().getConexion();
    }

    public Cliente getById(int idcliente) throws Exception {
        Cliente cliente = new Cliente();
        try {
            mysql.setAutoCommit(false);
            String consulta = "SELECT idcliente,nombre,paterno,materno,clave,usuario FROM cliente WHERE idcliente = ?";
            PreparedStatement pstmt = mysql.prepareStatement(consulta);
            pstmt.setInt(1, idcliente);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                cliente.setIdcliente(rs.getInt(1));
                cliente.setNombre(rs.getString(2));
                cliente.setPaterno(rs.getString(3));
                cliente.setMaterno(rs.getString(4));
                cliente.setClave(rs.getString(5));
                cliente.setUsuario(rs.getString(6));
            }

            mysql.setAutoCommit(true);

        } catch (Exception e) {
            mysql.rollback();
            System.out.println(e.getMessage());
            throw new Exception("DB : error  entregando cliente getById");
        } finally {
            try {
                System.out.println("entrega de clientes getById satisfactorio");
                mysql.close();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
        return cliente;
    }

    public List<Cliente> getAll() throws Exception {

        Cliente cliente = null;
        List<Cliente> lstClientes = null;
        try {
            mysql.setAutoCommit(false);
            String consulta = "SELECT idcliente,nombre,paterno,materno FROM cliente";
            PreparedStatement pstmt = mysql.prepareStatement(consulta);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null) {
                lstClientes = new ArrayList<Cliente>();
            }

            while (rs.next()) {
                cliente = new Cliente();
                cliente.setIdcliente(rs.getInt(1));
                cliente.setNombre(rs.getString(2));
                cliente.setPaterno(rs.getString(3));
                cliente.setMaterno(rs.getString(4));
                lstClientes.add(cliente);
            }
            mysql.setAutoCommit(true);

        } catch (Exception e) {
            mysql.rollback();
            System.out.println(e.getMessage());
            throw new Exception("DB : error  entregando cliente getById");
        } finally {
            try {
                System.out.println("entrega de clientes getById satisfactorio");
                mysql.close();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
        return lstClientes;
    }

    public List<Cliente> getLike(String cadena) throws Exception {

        Cliente cliente = null;
        List<Cliente> lstClientes = null;
        try {
            mysql.setAutoCommit(false);
            cadena = "%" + cadena + "%";
            String consulta = "SELECT idcliente,nombre,paterno,materno FROM cliente "
                    + "WHERE nombre LIKE ? || paterno LIKE ? || materno LIKE ? ORDER BY nombre,paterno,materno";
            PreparedStatement pstmt = mysql.prepareStatement(consulta);
            pstmt.setString(1, cadena);
            pstmt.setString(2, cadena);
            pstmt.setString(3, cadena);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null) {
                lstClientes = new ArrayList<Cliente>();
            }
            while (rs.next()) {
                cliente = new Cliente();
                cliente.setIdcliente(rs.getInt(1));
                cliente.setNombre(rs.getString(2));
                cliente.setPaterno(rs.getString(3));
                cliente.setMaterno(rs.getString(4));
                lstClientes.add(cliente);
            }
            mysql.setAutoCommit(true);
        } catch (Exception e) {
            mysql.rollback();
            System.out.println(e.getMessage());
            throw new Exception("DB : error  entregando cliente getLike");
        } finally {
            try {
                System.out.println("entrega de clientes getLike satisfactorio");
                mysql.close();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }

        return lstClientes;
    }

    @Override
    public Cliente getByLogin(String usuario, String clave) throws Exception {
          Cliente cliente = new Cliente();
        try {
            mysql.setAutoCommit(false);
            String consulta = "SELECT idcliente,nombre,paterno,materno,usuario,clave"
                            +" FROM cliente WHERE usuario = ? AND clave = ?";
            PreparedStatement pstmt = mysql.prepareStatement(consulta);
            pstmt.setString(1, usuario);
            pstmt.setString(2, clave);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                cliente.setIdcliente(rs.getInt(1));
                cliente.setNombre(rs.getString(2));
                cliente.setPaterno(rs.getString(3));
                cliente.setMaterno(rs.getString(4));
                cliente.setUsuario(rs.getString(5));
                cliente.setClave(rs.getString(6));
            }
            mysql.setAutoCommit(true);
        } catch (Exception e) {
            mysql.rollback();
            System.out.println(e.getMessage());
            throw new Exception("DB : error  entregando cliente getById");

        } finally {
            try {
                System.out.println("entrega de clientes getById satisfactorio");
                mysql.close();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
        return cliente;
    }
}
